﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace Plugin
{
    public class TempSensorThread
    {
        public int LimitSize { get; set; }

        private string sCxn = "server=MARCO-TIMELINE\\SQLEXPRESS;User ID=swe_user;Password=swe123;Initial Catalog=swe_database";
        private SqlConnection myConnection;

        public TempSensorThread()
        {
            LimitSize = 30;
            myConnection = new SqlConnection(sCxn);
        }
        public void Run()
        {
            Random rnd = new Random();
            try
            {
                while (true)
                {
                    myConnection.Open();
                    SqlCommand myCommand = new SqlCommand(@"INSERT INTO temperatur (grad) VALUES (@param1)", myConnection);
                    myCommand.Parameters.AddWithValue("@param1", rnd.Next(-20, 45));
                    myCommand.ExecuteNonQuery();
                    myConnection.Close();
                    Console.WriteLine("SensorThread: inserted new data into database.");
                    Thread.Sleep(60000);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Temperature-Plugin\nOccurred in SensorThread: " + e.Message);
            }
            finally
            {
                if(myConnection != null)
                    myConnection.Close();
            }

        }
        public List<string[]> getTempsOfDay(DateTime date)
        {
            Console.WriteLine("SensorThread: date: " + date.ToShortDateString());
            List<string[]> list = new List<string[]>();
            SqlDataReader reader = null;
            SqlConnection myConnection = null;
            try
            {
                myConnection = new SqlConnection(sCxn);
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand("SELECT datum, grad FROM temperatur WHERE (select CONVERT(date, datum))=@param1 ORDER BY datum", myConnection);

                SqlParameter dateTimeParam = new SqlParameter("@param1", SqlDbType.DateTime);
                dateTimeParam.Value = date;
                myCommand.Parameters.Add(dateTimeParam);
                
                reader = myCommand.ExecuteReader();

                while (reader.Read())
                {
                    string[] sary = new string[2];
                    sary[0] = reader.GetDateTime(0).ToString();
                    sary[1] = reader.GetDecimal(1).ToString();
                    list.Add(sary);
                }
                return list;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Temperature-Plugin\nOccurred in SensorThread: " + e.Message + "\n" + e.StackTrace);
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (myConnection != null)
                    myConnection.Close();
            }
        }
        public List<string[]> getTempsByLimit(int start)
        {
            if (start < 0)
                return null;

            List<string[]> list = new List<string[]>();
            SqlDataReader reader = null;
            SqlConnection myConnection = null;
            try
            {
                myConnection = new SqlConnection(sCxn);
                myConnection.Open();

                SqlCommand myCommand = new SqlCommand("SELECT TOP (@limit) datum, grad FROM temperatur WHERE id NOT IN (SELECT TOP (@von) id FROM temperatur ORDER BY datum) ORDER BY datum", myConnection);

                myCommand.Parameters.AddWithValue("@von", start);
                myCommand.Parameters.AddWithValue("@limit", this.LimitSize);
                Console.WriteLine("start: " + start + "\nLimitSize: " + this.LimitSize);
                
                reader = myCommand.ExecuteReader();

                while (reader.Read())
                {
                    string[] sary = new string[2];
                    sary[0] = reader.GetDateTime(0).ToString();
                    sary[1] = reader.GetDecimal(1).ToString();
                    list.Add(sary);
                }
                return list;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Temperature-Plugin\nOccurred in SensorThread: " + e.Message + "\n" + e.StackTrace);
                return null;
            }
            finally
            {
                if (reader != null)
                    reader.Close();
                if (myConnection != null)
                    myConnection.Close();
            }
        }
        public int getNumberOfRowsInDb()
        {
            SqlConnection myConnection = null;
            try
            {
                myConnection = new SqlConnection(sCxn);
                myConnection.Open();
                SqlCommand myCommand = new SqlCommand("select count(id) from temperatur", myConnection);

                int x = (int) myCommand.ExecuteScalar();
                return x;
            }
            catch (Exception e)
            {
                Console.WriteLine("Error: Temperature-Plugin\nOccurred in SensorThread: " + e.Message + "\n" + e.StackTrace);
                return -1;
            }
            finally
            {
                if (myConnection != null)
                    myConnection.Close();
            }
        }
    }
}
